﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace PageAdmin.Utils.ORM
{

    /// <summary>
    /// 数据表中跟数据操作有关的如增，删，改，查的基础扩展方法
    /// </summary>
    public static class DbDataBasicMethod
    {

        #region 数据增删改查的基础方法

        /// <summary>
        /// 运行sql语句
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="sql">sql语句</param>
        /// <returns>发挥执行成功数</returns>
        public static int Execute(this DataBaseContext dbContext, string sql)
        {
            IDbInstance db = dbContext.DbInstance;
            var dbParameters = dbContext.DbParamterList;
            int nums = db.Execute(sql, dbParameters);
            dbContext.ExecutedHandel();
            return nums;
        }

        /// <summary>
        /// 执行一组sql语句
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="sqls"></param>
        public static void Execute(this DataBaseContext dbContext, IEnumerable<string> sqls)
        {
            if (sqls.Count() > 0)
            {
             
                try
                {
                    foreach (string sql in sqls)
                    {
                        if (!string.IsNullOrEmpty(sql))
                        {
                            dbContext.Execute(sql);
                        }
                    }
                  
                }
                catch (SqlException ex)
                {
      
                    throw new Exception(ex.Message);
                }
            }

        }
        /// <summary>
        /// 运行sql，返回IDataReader
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="sql">执行的sql语句</param>
        /// <returns></returns>
        public static IDataReader GetReader(this DataBaseContext dbContext, string sql)
        {
            IDbInstance db = dbContext.DbInstance;
            var dbParameters = dbContext.DbParamterList;
            IDataReader dr = db.GetReader(sql, dbParameters);
            dbContext.ExecutedHandel();
            return dr;
        }


        /// <summary>
        /// 运行sql，返回DataTable
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="sql">执行的sql语句</param>
        /// <returns></returns>
        public static DataTable GetDataTable(this DataBaseContext dbContext, string sql)
        {
            IDbInstance db = dbContext.DbInstance;
            var dbParameters = dbContext.DbParamterList;
            DataTable dt = db.GetDataTable(sql, dbParameters);
            dbContext.ExecutedHandel();
            return dt;
        }

        /// <summary>
        /// 获取第一条记录的第一个字段的值，返回object类型,DbNull和或找不到都返回null
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="sql">执行的sql语句</param>
        /// <returns></returns>
        public static object GetField(this DataBaseContext dbContext, string sql)
        {
            IDbInstance db = dbContext.DbInstance;
            var dbParameters = dbContext.DbParamterList;
            object rv = db.GetField(sql, dbParameters);
            dbContext.ExecutedHandel();
            return rv;
        }

        /// <summary>
        /// 检测字段值是否存在
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <param name="field">字段</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="sqlCondition">查询条件,不需要加where</param>
        /// <returns></returns>
        public static bool IsExists(this DataBaseContext dbContext, string table, string field, dynamic fieldValue, string sqlCondition = null)
        {
            string fieldWhere = " [" + field + "]=@field";
            if (!string.IsNullOrWhiteSpace(sqlCondition))
            {
                fieldWhere += " and " + sqlCondition;
            }
            dbContext.AddDbParamter("@field", fieldValue);
            string schmea=dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            string sql = $"if exists(select * from [{table}] where {fieldWhere}) select '1' else select '0'";
            string result = (string)dbContext.GetField(sql);
            return result == "1" ? true : false;
        }


        /// <summary>
        ///  检测记录是否存在
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="table"></param>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        public static bool IsExists(this DataBaseContext dbContext, string table, string sqlCondition = null)
        {
            if (sqlCondition != null)
            {
                sqlCondition = " where " + sqlCondition;
            }
            //if exists(select * from tableName where conditions) select '1' else select '0'
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            string sql = $"if exists(select * from [{table}] {sqlCondition}) select '1' else select '0'";
            string result = (string)dbContext.GetField(sql);
            return result == "1" ? true : false;
        }

        /// <summary>
        /// 查询，返回IDataReader
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <param name="fields">要查询的字段，设为null则读取所有字段</param>
        /// <param name="sqlCondition">查询条件,不需要加where</param>
        /// <param name="sqlOrderBy">排序，不需要加order by,如：id asc</param>
        /// <returns></returns>
        public static IDataReader GetReader(this DataBaseContext dbContext, string table, string fields, string sqlCondition = null, string sqlOrderBy = null)
        {
            if (string.IsNullOrEmpty(fields))
            {
                fields = "*";
            }
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            string sql = BuildSqlHelper.CreateSelectSql(table, fields, sqlCondition, sqlOrderBy);
            return dbContext.GetReader(sql);
        }


        /// <summary>
        /// 查询字段的值,返回object类型，查不到或值为DbNull都返回null
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <param name="field">字段</param>
        /// <param name="sqlCondition">查询条件,不需要加where</param>
        /// <param name="sqlOrderBy">排序，不需要加order by,如：id asc</param>
        /// <returns></returns>
        public static object GetField(this DataBaseContext dbContext, string table, string field, string sqlCondition = null, string sqlOrderBy = null)
        {
            if (string.IsNullOrEmpty(field))
            {
                throw new Exception("无效的field参数！");
            }
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            string sql = BuildSqlHelper.CreateSelectSql(table, field, sqlCondition, sqlOrderBy);
            return dbContext.GetField(sql);
        }

        /// <summary>
        /// 插入一条新的数据,并获取最新插入的Id
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <param name="dbKeyValueList">字段名，字段值组成的KeyValuePair集合</param>
        /// <param name="returnNewId">是否需要返回最新的id，默认未false</param>
        /// <returns></returns>
        public static int Insert(this DataBaseContext dbContext, string table, DbKeyValueList dbKeyValueList, bool returnNewId = true)
        {
            if (dbKeyValueList == null)
            {
                return 0;
            }
            string fields = "", values = "";
            int i = 0;
            string paramterKey;
            dbContext.SetDbParamter(null);
            foreach (var item in dbKeyValueList)
            {
                if (i > 0)
                {
                    fields += ",";
                    values += ",";
                }
                paramterKey = "@InsertParamter" + i;
                fields += "[" + item.Key + "]";
                values += paramterKey;
                dbContext.AddDbParamter(paramterKey, item.Value, item.Size);
                i++;
            }
            if (i == 0) { return 0; }
            int newId = 0;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            string sql = "insert into [" + table + "](" + fields + ") values(" + values + ")";
            if (returnNewId)
            {
                if (dbContext.DbType == DataBaseType.MsSql)
                {
                    sql += " select SCOPE_IDENTITY()";
                    newId = Convert.ToInt32(dbContext.GetField(sql));
                }
                else
                {

                }
            }
            else
            {
                newId = dbContext.Execute(sql);
            }
            return newId;
        }


        /// <summary>
        /// 根据id删除
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="table"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int Delete(this DataBaseContext dbContext, string table, int id)
        {
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            dbContext.SetDbParamter("id", id);
            string sql = "delete from [" + table + "] where id=@id";
            return dbContext.Execute(sql);
        }


        /// <summary>
        /// 根据条件删除数据
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">删除的表名</param>
        /// <param name="sqlCondition">条件语句</param>
        /// <returns></returns>
        public static int Delete(this DataBaseContext dbContext, string table, string sqlCondition)
        {
            if (!string.IsNullOrWhiteSpace(sqlCondition))
            {
                sqlCondition = " where " + sqlCondition;
            }
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            string sql = "delete from [" + table + "]" + sqlCondition;
            return dbContext.Execute(sql);
        }


        /// <summary>
        /// 更新表中的单个字段
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <param name="field">字段</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="sqlCondition">查询条件,不需要加where</param>
        /// <returns></returns>
        public static int Update(this DataBaseContext dbContext, string table, string field, dynamic fieldValue, string sqlCondition)
        {
            if (!string.IsNullOrWhiteSpace(sqlCondition))
            {
                sqlCondition = " where " + sqlCondition;
            }
            dbContext.AddDbParamter("@updateParamter0", fieldValue);
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            return dbContext.Execute("update [" + table + "] set [" + field + "]=@updateParamter0" + sqlCondition);
        }

        /// <summary>
        /// 更新表中的多个字段
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <param name="dbKeyValueList">字段名，字段值组成的KeyValuePair集合</param>
        /// <param name="condition">更新条件限制,不需要加where，为null表示更新整个数据表</param>
        /// <returns></returns>
        public static int Update(this DataBaseContext dbContext, string table, DbKeyValueList dbKeyValueList, string condition)
        {
            if (dbKeyValueList == null)
            {
                return 0;
            }
            DbKeyValueList outParam = new DbKeyValueList();
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            string sql = BuildSqlHelper.CreateUpdateSql(table, dbKeyValueList, condition, out outParam);;
            foreach (var item in outParam)
            {
                dbContext.AddDbParamter(item.Key, item.Value, item.Size);
            }
            return dbContext.Execute(sql);
        }


        /// <summary>
        /// 复制表中的数据,目标表必须存在
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="insertTable">要插入的表</param>
        /// <param name="insertFields">要插入的字段</param>
        /// <param name="selectTable"></param>
        /// <param name="selectFields"></param>
        /// <param name="selectCondition">要查询或复制的值字段，为null时表示和fields一致</param>
        public static void CopyTableData(this DataBaseContext dbContext, string insertTable, string insertFields, string selectTable, string selectFields = "*", string selectCondition = null)
        {
            if (insertFields == "*")
            {
                insertFields = string.Join(",", dbContext.GetTableFields(insertTable));
            }
            if (selectFields == "*")
            {
                if (selectTable == insertTable)
                {
                    selectFields = insertFields;
                }
                else
                {
                    selectFields = string.Join(",", dbContext.GetTableFields(selectTable));
                }
            }
            if (!string.IsNullOrEmpty(selectCondition))
            {
                selectCondition = " where " + selectCondition;
            }
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                insertTable = schmea + "." + insertTable;
                selectTable = schmea + "." + insertTable;
            }
            insertFields = BuildSqlHelper.FormatField(insertTable,insertFields);
            selectFields = BuildSqlHelper.FormatField(selectTable,selectFields);
            string sql = "insert into [" + insertTable + "](" + insertFields + ") select " + selectFields + " from [" + selectTable + "]" + selectCondition;
            dbContext.Execute(sql);
        }

        #endregion
    }
}
